package com.lyzd.dbtools.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.lyzd.dbtools.entity.*;
import com.lyzd.dbtools.entity.Table;
import com.lyzd.dbtools.service.MySqlService;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@AllArgsConstructor
@Slf4j
@Controller
public class HomeController {
    private final MySqlService mySqlService;

    @RequestMapping("/")
    public String index(Model model) {
        List<DataBase> schemaList = mySqlService.queryAllDataBase();
        model.addAttribute("schemaList", schemaList);
        return "Index";
    }

    @PostMapping("/exportSchema")
    public void exportSchema(@RequestParam(value="schemaList[]") String[] schemaList, HttpServletResponse response) throws IOException {
        if (schemaList == null || schemaList.length == 0) {
            return;
        }

        ExcelWriter writer = ExcelUtil.getWriter(true);
        Map<String, List<Catalog>> catalogMap = CollUtil.newHashMap(16, true);
        writeVersionAndCatalog(writer);
        for (String schema : schemaList) {
            catalogMap.put(schema, writeSchema(writer, schema));
        }
        writeCatalog(writer, catalogMap);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=export_mysql.xlsx");

        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }

    public void writeVersionAndCatalog(ExcelWriter writer) {
        writer.renameSheet("版本记录");
        writer.writeHeadRow(CollUtil.toList("序号", "修改内容", "修改人", "日期"));

        writer.setSheet("目录");
    }

    // 目录
    public void writeCatalog(ExcelWriter writer, Map<String, List<Catalog>> catalogMap) {
        writer.setSheet("目录");
        writer.resetRow();
        writer.writeHeadRow(CollUtil.toList("数据库", "表名", "备注"));

        CreationHelper helper = writer.getWorkbook().getCreationHelper();
        CellStyle hyperLinkStyle = writer.getWorkbook().createCellStyle();
        hyperLinkStyle.cloneStyleFrom(writer.getCellStyle());
        Font hlinkFont = writer.getWorkbook().createFont();
        hlinkFont.setUnderline(Font.U_NONE);
        hlinkFont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        hyperLinkStyle.setFont(hlinkFont);

        for (Map.Entry<String, List<Catalog>> entry : catalogMap.entrySet()) {
            int firstRow = writer.getCurrentRow();
            for (Catalog catalog : entry.getValue()) {
                writer.writeCellValue(1, writer.getCurrentRow(), catalog.getTableName());
                writer.writeCellValue(2, writer.getCurrentRow(), catalog.getTableComment());
                Cell cell = writer.getCell(1, writer.getCurrentRow());
                Hyperlink link = helper.createHyperlink(HyperlinkType.DOCUMENT);
                link.setAddress(catalog.getAddress());
                cell.setHyperlink(link);
                cell.setCellStyle(hyperLinkStyle);
                writer.passCurrentRow();
            }
            int lastRow = writer.getCurrentRow() - 1;
            if (firstRow == lastRow) {
                writer.writeCellValue(0, firstRow, entry.getKey());
            } else {
                writer.merge(firstRow, lastRow, 0, 0, entry.getKey(), false);
            }
        }
        writer.autoSizeColumnAll();
    }

    // 数据库
    public List<Catalog> writeSchema(ExcelWriter writer, String schemaName) {
        List<Table> tableList = mySqlService.queryTablesByDbName(schemaName);
        if (CollUtil.isEmpty(tableList)) {
            return Collections.emptyList();
        }
        // 设置schema名称为Excel的sheet名称
        writer.setSheet(schemaName);
        writer.setOnlyAlias(true);
        StyleSet style = writer.getStyleSet();
        CellStyle cellStyle = style.getCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        List<Catalog> catalogList = CollUtil.newArrayList();
        for (Table table : tableList) {
            catalogList.add(writeTable(writer, schemaName, table));
        }
        return catalogList;
    }

    // 单个数据表
    private Catalog writeTable(ExcelWriter writer, String schemaName, Table table) {
        writer.writeCellValue(0, writer.getCurrentRow(), table.getTableName());

        Cell cell = writer.getCell(0, writer.getCurrentRow());

        Catalog catalog = Catalog.builder().tableName(table.getTableName())
                .tableComment(table.getTableComment())
                .address(StrUtil.format("'{}'!{}", schemaName, cell.getAddress().formatAsString()))
                .build();

        writer.passRows(2);

        writer.writeCellValue(0, writer.getCurrentRow(), StrUtil.concat(true, "备注：", table.getTableComment()));
        writer.passRows(2);

        writer.writeCellValue(0, writer.getCurrentRow(), "索引：");
        writer.passRows(2);
        List<Index> indexList = mySqlService.queryIndexByDbAndTableName(schemaName, table.getTableName());
        Map<String, String> indexHeaderMap = CollUtil.newHashMap(6, true);
        indexHeaderMap.put("keyName", "名称");
        indexHeaderMap.put("columnName", "类型");
        indexHeaderMap.put("nonUnique", "其他");
        indexHeaderMap.put("extra", "备注");
        writer.setHeaderAlias(indexHeaderMap);
        writer.write(indexList, true);
        writer.autoSizeColumnAll();
        writer.passRows(2);

        writer.writeCellValue(0, writer.getCurrentRow(), "字段：");
        writer.passRows(2);
        List<Column> columnList = mySqlService.queryColumnByDbAndTableName(schemaName, table.getTableName());
        if (CollUtil.isEmpty(columnList)) {
            return catalog;
        }

        Map<String, String> headerMap = CollUtil.newHashMap(6, true);
        headerMap.put("filed", "名称");
        headerMap.put("type", "类型");
        headerMap.put("collation", "排序规则");
        headerMap.put("isNull", "空");
        headerMap.put("key", "主键");
        headerMap.put("defaultValue", "默认值");
        headerMap.put("extra", "其他");
        headerMap.put("comment", "备注");
        writer.setHeaderAlias(headerMap);
        writer.write(columnList, true);
        writer.autoSizeColumnAll();

        writer.passRows(2);

        return catalog;
    }
}
